#!/usr/bin/python
# -*- coding: UTF-8 -*-


import pandas as pd
import xlrd
import xlwt
import os
from xlutils.copy import copy

"""
源文件名字固定为1.xls
源文件sheet页固定为Sheet1

对比文件名字固定为2.xls
对比文件sheet页固定为Sheet1

结果文件名固定为匹配结果.xls
结果文件sheet页固定为“匹配结果”

注：源文件为数据较多文件；对比文件为数据较少文件
source_col及compare_col为两个文件对比的列号，起始位是1
"""

#按序返回并集，交集，差集，源信息列表，对比信息列表
#并集A∪B，交集A∩B，差集A∪B-A∩B
def compare(source_col=0, compare_col=0):
    source_file = r'C:\Users\Administrator\Desktop\1.xls'
    compare_file = r'C:\Users\Administrator\Desktop\2.xls'

    source = pd.read_excel(source_file).iloc[:, source_col - 1:source_col]
    compare = pd.read_excel(compare_file).iloc[:, compare_col - 1:compare_col]
    source_list = source.values.tolist()
    compare_list = compare.values.tolist()
    source_list = [i for list in source_list for i in list]
    compare_list = [i for list in compare_list for i in list]
    #求出两个list的并集
    union_list = list(set(source_list).union(set(compare_list)))
    #求出两个list的交集
    intersection_list = list(set(source_list).intersection(set(compare_list)))
    #求出两个list的差集
    difference_list = list(set(source_list).difference(set(compare_list)))
    return (union_list,intersection_list,difference_list,source_list,compare_list)

#print(compare(5,5))
#输出至xls，通过newfile判断是在原始文件上修改还是直接新建匹配结果文件
#代码中使用了compare函数，注意修改列值
def w2xls(newfile = 0):
    if newfile == 0:
        return 'not newfile'
    else:
        mess = compare(5,5)
        if mess is not None:
            union = mess[0]
            inter = mess[1]
            diff = mess[2]
            sou = mess[3]
            comp = mess[4]
            print(union)





        return 'newfile'